#!/usr/bin/python3
import pymysql
import os
import base64

# 打开数据库连接
db = pymysql.connect("15.112.148.231", "root", "Kc@123456", "sits_sit")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute()  方法执行 SQL 查询
cursor.execute("select * from as_person_image t where length(t.image_back_byte) > 200;")

# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchall()
exportDir = "E:\\images_temp\\20191023\\"
filePath = {}
for da in data:
    id = da[0]
    base = da[1]
    byteBase = da[5]
    if base:
        basea = base64.b64decode(base);
    if byteBase:
        byteBase64 = base64.b64decode(byteBase);
    filebasePath = exportDir + id + "_base.jpg"
    filebasePath64 = exportDir + id + "_see.jpg"
    base64file = open(filebasePath, "wb")
    byteBase64file = open(filebasePath64, "wb")
    base64file.write(basea);
    byteBase64file.write(byteBase64)
    filePath[id] = filebasePath + "," + filebasePath64
    base64file.close()
    byteBase64file.close()
namesql = []
for files in filePath:
    path = filePath[files]
    split = path.split(",")
    imagesPath = split[0]
    imagesPath64 = split[1]
    sql = "update as_person_image set image_base = '" + imagesPath + "',image_back_byte = '" + imagesPath64 + "'WHERE image_id = " + "'"+files+"'"
    namesql.append(sql)

file = open('E:\\images_temp\\sql.txt', 'w', encoding='UTF-8')
for ni in namesql:
    file.writelines("%s\n" % ni)

file.close()

# 关闭数据库连接
db.close()
